] image to download the Data Warehouse script. Click the download [
] image to download the Data Warehouse script.
Extract the file and execute the scripts in MSSQL Server Management.
Open the extracted folder and you will see two folders ( StoredProcedure and Tables ) as shown below.
Open script with SSMS 18 then Executes the scripts one after the other till all three scripts have been executed.
You may also open in Notepad, copy the content into SSMS and execute to build all four database objects.
Execute all three scripts in the folder like shown below
Next, open the StoredProcedure Folder and Execute the script to generate the Stored Procedure as shown below
Verify that all four data objects can be seen in the Object explorer as shown below
If you do not see all objects, it means you omitted some of the steps. review and make the necessary corrections before proceeding.
However, if all objects can be seen then we have completed our first task which is DataBase and Object creation.
Building SSIS Package to load the Fact Table.
Building SSIS Package t
Add a new package as shown in image below
Rename the package to make it more descriptive
Double click on the newly created package and then begin to drag and drop our Data flow Task as shown below and rename the package to make it discriptive.
The DataFlow task workspace will be open. Drag and Drop an OLE DB Source like shown below.
Double click to begin with configuration . Select Connection Manager. Click New and establish the
Configure the Connection Manager like shown below then click Ok when done.
Below is the script to use
select [CCS_PR_ID], [Created], [UserID], [PreUserID], [CCS_PR_Approved],[Username], case [CCS_PR_Approved] when 0 then 0 when 1 then 1 when 2 then 1 end FristApprovalStatus, Case [CCS_PR_Approved] when 0 then 0 when 1 then 0 when 2 then 1 end SecondApprovalStatus, [Updated], [UpdatedBy] from [dbo].[ADF_CCS_ProjectRequest] CPP left join [dbo].[ADF_Users] ADU on CPP.CreatedBy = ADU.[UserID]
Click Ok when done.
Drag and drop a Lookup transformational nd connect the OLEDB Source as shown in the image below and apply the configuration below.
Add another lookup transformation and name it TagKey as shown below. Then like the projectKey Lookup to it
Double click on the TagKey and configure as shown below
Click Ok when done configuring the TagKey LookUp.
Add another LookUp transformation and configure as shown below.
Double click on the UserKey to begin configuration as sown below
Add another LookUp transformation as shown below
Now for Non-Matching Output, we will be connecting to OLE DB Destination as shown below
Double-click on the OLE DB Destination and begin to configure as shown below.
Double Click on the lookUp transformation as shown below and configure as shown
Configure the columns and when done, click Ok to save the configuration
Add OLE DB Command and connect to the LookUp as shown below and click Ok.
Double click on the OLE DB Command and begin to configure as shown below.
Update [dbo].[FactProjectStatus]
set
[Created] =?,
[CreatedBy]=?,
[FristApprovalStatus]=?,
[SecondApprovalStatus]=?,
[Updated]=?,
[UpdatedBy] =?
where [ProjectStatusKey] =?
Perform the following Mapping and click Ok when done.
Drag and drop dataFlow task on the control Flow as seen in the image, rename and double click to begin configuration.
In the Data Flow task drag and drop an OLE DB Source and begin to configure as shown then click Ok when done
use the script below
with cte_FactClick
as
(
select [CCS_PR_ID], [CCS_PR_UserID], [CCS_PR_Approved], [IsActive], [ispublic],
[TagID], [PreUserID],[CreatedDate],
ACT.[Id],[StepOrder],[SourceID], [ProjectTagID]
from [dbo].[ADF_Pre_Users] APU
left join [dbo].[ADF_CCS_ProjectRequest] CPR
on CPR.[CCS_PR_UserID] = APU.[ID]
left join [dbo].[ADF_CCS_ClickThrough_Tracking] ACT
on CPR.CCS_PR_ID = ACT.ProjectId
left join [dbo].[ADF_CCS_ProjectTags] CPT
on CPR.CCS_PR_ID = CPT.ProjectID
)
select * from cte_FactClick
Add a LookUp transformation, rename and connect OLE DB source to it as shown. Double-Click and begin to configure.
Add another LookUp Transformation and begin to configure like shown
Double-Click and begin to configure as shown below
Add another LoookUp transformation as shown below and begin to configure
Double click on the PreUserKey LookUp transformation and begin to configure as shown below
Add another KookUp Transformation, name it UserKey and connect to PreUser Transformation as shown below.
Double Click the Userkey LookUp as shown below
Now that we have derived all the Keys, let us check if the record already exists by performing another LookUp Transformation as shown below.
Click Ok when done then add OLE DB Destination as shown below and link all Non-Matching output like shown below.
Double click on the OLE Destination and configure as shown below
Click Ok when done.
It is now time to configure all matching output by adding another LookUp Transformation then rename it to UpdateRecords as shown below
Double click to begin configuration as shown below.
Click Ok when done. Add OLE DB Command like shown below and connect all Non-Matching Records from the LookUp Transformation to it as shown below
Double click on the OLE DB Command and configure as shown
Click Ok when done.
This time we will be using Execute SQL Task to call the Stored Procedure "proc_Load_FactClickThorughPenetrationRate" we executed earlier on.
Add an Execute SQL Task and rename it as shown below.
Double on the Execute SQL Task and select the data connection as shown below
Next begin with configuration as shown below
Types of Fact Tables:
https://www.nuwavesolutions.com/fact-tables/
Populating Fact Tables:
Fact Table Loading:
https://mindmajix.com/ssis/fact-table-loading